﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IAC_2013.com.iac.form;
using IAC_2013.com.iac.util;
using System.Data;
using System.Data.SqlClient;

namespace IAC_2013.com.iac.action
{
    class ProductAction
    {
        SqlConnection conn = new SqlConnection(DBUtils.CONNECTION_STRING);
        SqlCommand cmd;
        SqlDataAdapter da;
        DataTable tb;
        public Boolean addProduct(ProductForm frm)
        {
            string sql = "insert into sp_san_pham values(";
            sql += "'"+ frm.productCode +"', ";
            sql += "N'"+ frm.productName +"', ";
            sql += frm.unitId + ", ";
            sql += frm.categoryProductId + ", ";
            sql += "N'"+ frm.productStandard +"', ";
            sql += "'"+ frm.productSize +"', ";
            sql += "N'"+ frm.productDescription +"', ";
            sql += 1;
            sql += ")";

            try
            {
                cmd = new SqlCommand(sql, conn);
                conn.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public Boolean editProduct(ProductForm frm)
        {
            string sql = "update sp_san_pham set ";
            sql += " ma_sp = '" + frm.productCode + "', ";
            sql += " ten_sp = N'"+ frm.productName +"', ";
            sql += " id_dv = " + frm.unitId + ", ";
            sql += " id_lsp = " + frm.categoryProductId + ", ";
            sql += " quy_cach_sp = '"+ frm.productStandard +"', ";
            sql += " kich_thuoc_sp = '"+ frm.productSize +"', ";
            sql += " mo_ta = N'"+ frm.productDescription +"' ";
            sql += " where ";
            sql += " id_sp = " + frm.productId;

            try
            {
                cmd = new SqlCommand(sql, conn);
                conn.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public Boolean deleteProduct(ProductForm frm)
        {
            string sql = "delete from sp_san_pham where id_sp = " + frm.productId;

            try
            {
                cmd = new SqlCommand(sql, conn);
                conn.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public DataTable searchProduct(ProductForm frm)
        {
            tb = new DataTable();
            string sql = "select sp.id_sp, sp.ma_sp, sp.ten_sp, sp.quy_cach_sp, sp.kich_thuoc_sp, sp.mo_ta, dv.id_dv, dv.ten_dv, lsp.id_lsp, lsp.ten_loai_sp ";
            sql += " from sp_san_pham sp, sp_loai_san_pham lsp, dv_don_vi dv ";
            sql += " where 1 = 1 ";
            sql += " and sp.id_dv = dv.id_dv ";
            sql += " and sp.id_lsp = lsp.id_lsp ";
            if (frm.productCode != null && !frm.productCode.Trim().Equals(""))
            {
                sql += " and LOWER(sp.ma_sp) LIKE '"+ "%" + frm.productCode + "%" +"' ";
            }
            if (frm.productName != null && !frm.productName.Trim().Equals(""))
            {
                sql += " and LOWER(sp.ten_sp) LIKE '"+ "%" + frm.productName + "%" +"' ";
            }
            if (frm.productDescription != null && !frm.productDescription.Trim().Equals(""))
            {
                sql += " and LOWER(sp.mo_ta) LIKE '%"+ frm.productDescription +"%' ";
            }
            if (frm.productSize != null && !frm.productSize.Trim().Equals(""))
            {
                sql += " and LOWER(sp.kich_thuoc_sp) LIKE '%"+ frm.productSize +"%' ";
            }
            if (frm.productStandard != null && !frm.productStandard.Trim().Equals(""))
            {
                sql += " and LOWER(sql.quy_cach_sp) LIKE '%"+ frm.productStandard +"%' ";
            }
            sql += " and trang_thai = " + 1;
            sql += " order by sp.id_sp desc";
            try
            {
                cmd = new SqlCommand(sql, conn);
                conn.Open();
                da = new SqlDataAdapter(cmd);
                tb = new DataTable();
                da.Fill(tb);
                cmd.Dispose();
                conn.Close();
                return tb;
            }
            catch (Exception)
            {
                return tb;
            }
        }
    }
}
